﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Sample_Read_DB
{
	public partial class fmCustomer : Form
	{
        private SqlConnection connection;
        private DataTable userTable;
        private SqlDataAdapter adapter;
		public fmCustomer()
		{
			InitializeComponent();
            btnSave.Enabled = false;
		}

		private void button1_Click(object sender, EventArgs e)
		{
			Console.WriteLine("Start loading data ... ");
            userDataGridView.DataSource = null;
            userDataGridView.Rows.Clear();
            userDataGridView.Refresh();
            connection = new SqlConnection(
				"user id=localhost;" +
                "password=bymyside;server=localhost;" + 
                "Trusted_Connection=yes;" +
				"database=MyDataBase; " + "connection timeout=30");

			try
			{
				// 2. Open the connection
                connection.Open();

				// 3. Pass the connection to a command object
                SqlCommand cmd = new SqlCommand("select * from Customers", connection);
                adapter = new SqlDataAdapter(cmd);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                DataSet ds = new DataSet("MainDataSet");
                DataSet tempDataSet = new DataSet("TempDataSet");
                adapter.Fill(tempDataSet);
                int totalRecords = tempDataSet.Tables[0].Rows.Count;
                txtboxNumofRe.Text = totalRecords.ToString();
                tempDataSet.Clear();
                tempDataSet.Dispose();
                adapter.Fill(ds, 0, 5, "Customers");
                userTable = ds.Tables["Customers"];

                foreach (DataColumn dc in userTable.Columns)
                {
                    DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
                    column.DataPropertyName = dc.ColumnName;
                    column.HeaderText = dc.ColumnName;
                    column.Name = dc.ColumnName;
                    column.SortMode = DataGridViewColumnSortMode.Automatic;
                    column.ValueType = dc.DataType;
                    userDataGridView.Columns.Add(column);
                }
                // enable button
                btnSave.Enabled = true;

                try
                {
                    userTable.Rows.Clear();
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    adapter.Fill(ds, 0, totalRecords, "Customers");
                    userTable = ds.Tables["Customers"];
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
                userDataGridView.DataSource = userTable.DefaultView;
			}
			finally
			{
				// 5. Close the connection
                if (connection != null)
				{
                    connection.Close();
				}
			}
			Console.WriteLine("Stop loading data ... ");	
		}

		private void BtnClose_Click(object sender, EventArgs e)
		{
			this.Close();
		}

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                adapter.Update(userTable);
                userDataGridView.ReadOnly = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                btnSave.Enabled = true;
            }
            finally
            {
                connection.Close();
            }
        }
	}
}
